package dao;

import java.sql.*;
import java.util.LinkedList;
import domain.Actor;
import domain.Movie;
import domain.User;
import domain.Role;
import ex.*;
import service.ActorService;
import util.*;

public class UserDAO {

	ActorService actorService=new ActorService();
	
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String AUTHENTICATE_USER="select * from user where email= ? and password= ? ";
	private static final String SELECT_USER_BY_UID="select * from user where uid= ? ";
	private static final String SELECT_USER_BY_EMAIL="select * from user where email= ? ";
	private static final String CREATE_USER="insert into user (email,password,firstName,lastName,dateOfBirth,imageURL,summary,city,tel,userTitle,rating,popularity,isAvailable,isReal,isFemale) values (?, ?, ?, ?, ?, ? ,?, ?, ?,?,?,?,?,?,?) ";
	private static final String EDIT_USER="UPDATE user SET email = ?, password = ?,firstName = ?,lastName = ?,dateOfBirth = ? ,imageURL = ?,summary = ?,city = ?,tel = ?, userTitle = ?, rating = ?,popularity = ?,isAvailable = ?,isReal = ?,isFemale = ? WHERE uid =? ";
	private static final String TOP_USER="Select Top n * from user Order by user.rating DESC where n = ?";
	// The above query isn't used in anywhere TODO
	private static final String SELECT_ACTED_MOVIES_BY_UID = "SELECT * FROM acted WHERE uid = ?";
	private static final String SELECT_DIRECTED_MOVIES_BY_UID = "SELECT mid FROM directed WHERE uid = ?";
	private static final String SELECT_MOVIES_WORKED_AS_CREW_BY_UID = "SELECT mid FROM worked_as_crew WHERE uid = ?";
	
	//Selection of the User using email and password info
	public User selectUserByEmailAndPassword(String email,String password) throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		
		User user = null;
		try {
			stmt = conn.prepareStatement(AUTHENTICATE_USER);
			stmt.setString(1, email);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				long uid = rs.getLong("uid");
				String firstname = rs.getString("firstName");
				String lastname = rs.getString("lastName");
				String imageURL = rs.getString("imageURL");
				Date dateOfBirth = rs.getDate("dateOfBirth");
				int title = rs.getInt("userTitle");
				int popularity = rs.getInt("popularity");
				double rating = rs.getInt("rating");
				String summary = rs.getString("summary");
				String city = rs.getString("city");
				String tel = rs.getString("tel");
				int available=rs.getInt("isAvailable");
				int isReal = rs.getInt("isReal");
				int isFemale = rs.getInt("isFemale");
				
				Actor actor=actorService.getActorByUid(uid);
				
				user=new User(uid,email,password, firstname,lastname,dateOfBirth,imageURL, summary,city,tel,title,rating,popularity, actor,available==1,isReal==1, isFemale==1);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving user information by email and password! "	+ e.getMessage());
		}
		return user;
	}
	
	//returns list of movies directed by user specified by uid
	public LinkedList<Movie> selectMoviesUserDirected(long uid) throws DatabaseException
	{
		User user = selectUserByUid(uid);
		
		LinkedList<Movie> directedMovies = new LinkedList<Movie>();
		
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(SELECT_DIRECTED_MOVIES_BY_UID);
			stmt.setLong(1, user.getUid());
			
			rs = stmt.executeQuery();
			MovieDAO movDAO = new MovieDAO();
			while(rs.next())
			{
				long mid = rs.getLong("mid");
				Movie movie = movDAO.selectMovieByMid(mid);
				directedMovies.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return directedMovies;	
	}
	
	//returns list of role objects(movies w/ actor/character info as in acted database relation/entity) associated w/ user 
	//specified by uid
	public LinkedList<Role> selectMoviesUserActedIn(long uid) throws DatabaseException
	{
		User user = selectUserByUid(uid);
		
		LinkedList<Role> moviesActedIn = new LinkedList<Role>();
		
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(SELECT_DIRECTED_MOVIES_BY_UID);
			stmt.setLong(1, user.getUid());
			
			rs = stmt.executeQuery();
			MovieDAO movDAO = new MovieDAO();
			while(rs.next())
			{
				long mid = rs.getLong("mid");
				Movie movie = movDAO.selectMovieByMid(mid);
				int status = rs.getInt("status");
				String character = rs.getString("character");
				
				Role role = new Role(user, movie, status == 1, character);
				moviesActedIn.add(role);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return moviesActedIn;
	}
	
	//returns list of movies user worked as a crew member
	public LinkedList<Movie> selectMoviesWorkedAsACrewMember(long uid) throws DatabaseException
	{
		User user = selectUserByUid(uid);
		
		LinkedList<Movie> moviesWorkedAsCrew = new LinkedList<Movie>();
		
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(SELECT_MOVIES_WORKED_AS_CREW_BY_UID);
			stmt.setLong(1, user.getUid());
			
			rs = stmt.executeQuery();
			MovieDAO movDAO = new MovieDAO();
			while(rs.next())
			{
				long mid = rs.getLong("mid");
				Movie movie = movDAO.selectMovieByMid(mid);
				moviesWorkedAsCrew.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return moviesWorkedAsCrew;	
	}

	
	public User selectUserByUid(long uid) throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		
		User user = null;
		try {
			stmt = conn.prepareStatement(SELECT_USER_BY_UID);
			stmt.setLong(1, uid);
			
			rs = stmt.executeQuery();
			if (rs.next()) {
				String email = rs.getString("email");
				String password = rs.getString("password");
				String firstname = rs.getString("firstName");
				String lastname = rs.getString("lastName");
				String imageURL = rs.getString("imageURL");
				String summary = rs.getString("summary");
				String city = rs.getString("city");
				String tel = rs.getString("tel");
				Date dateOfBirth = rs.getDate("dateOfBirth");
				int available=rs.getInt("isAvailable");
				int isReal = rs.getInt("isReal");
				int isFemale = rs.getInt("isFemale");
				int title = rs.getInt("userTitle");
				int popularity = rs.getInt("popularity");
				double rating = rs.getInt("rating");
				
				Actor actor=actorService.getActorByUid(uid);
				
				user=new User(uid,email,password, firstname,lastname,dateOfBirth,imageURL, summary,city,tel,title,rating,popularity, actor,available==1,isReal==1,isFemale==1);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving user information by uid! "	+ e.getMessage());
		}
		return user;
	}
	
	//checking if the email exists in the database
	public boolean checkUserByEmail(String userEmail) throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		boolean exists=false;
		// User variable isn't used in anywhere TODO
		User user = null;
		try {
			stmt = conn.prepareStatement(SELECT_USER_BY_EMAIL);
			stmt.setString(1, userEmail);
			
			rs = stmt.executeQuery();
			exists=rs.next();
			stmt.close();
			JDBCUtil.closeConnection(conn);					
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with checking user by email! "	+ e.getMessage());
		}
		return exists;
	}
	
	//Selection of the User using email
	public User selectUserByEmail(String userEmail) throws DatabaseException
	{
		conn = JDBCUtil.getConnection();
		
		User user = null;
		try {
			stmt = conn.prepareStatement(SELECT_USER_BY_EMAIL);
			stmt.setString(1, userEmail);
			
			rs = stmt.executeQuery();
			if (rs.next()) {
				long uid = rs.getLong("uid");
				String email = rs.getString("email");
				String password = rs.getString("password");
				String firstname = rs.getString("firstName");
				String lastname = rs.getString("lastName");
				String imageURL = rs.getString("imageURL");
				String summary = rs.getString("summary");
				String city = rs.getString("city");
				String tel = rs.getString("tel");
				Date dateOfBirth = rs.getDate("dateOfBirth");
				int available=rs.getInt("isAvailable");
				int isReal = rs.getInt("isReal");
				int isFemale = rs.getInt("isFemale");
				int title = rs.getInt("userTitle");
				int popularity = rs.getInt("popularity");
				double rating = rs.getInt("rating");
				
				Actor actor=actorService.getActorByUid(uid);
				
				user=new User(uid,email,password, firstname,lastname,dateOfBirth,imageURL, summary,city,tel,title,rating,popularity,actor,available==1,isReal==1,isFemale==1);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving user information by email! "	+ e.getMessage());
		}
		return user;
	}
	
	//Insertion of a new User to the database
	public void insertUser(User user) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(CREATE_USER);
			stmt.setString(1, user.getEmail());
			stmt.setString(2, user.getPassword());
			stmt.setString(3, user.getFirstName());
			stmt.setString(4, user.getLastName());
			stmt.setDate(5, (Date) user.getDateOfBirth());
			stmt.setString(6, user.getImageURL());
			stmt.setString(7, user.getSummary());
			stmt.setString(8, user.getCity());
			stmt.setString(9, user.getTel());
			stmt.setLong(10, user.getTitle());
			stmt.setDouble(11, user.getRating());
			stmt.setLong(12, user.getPopularity());
			stmt.setInt(13, user.isAvailable()?1:0);
			stmt.setInt(14, user.isReal()?1:0);
			stmt.setInt(15, user.isFemale()?1:0);
			
			stmt.execute();
			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with inserting user information! "	+ e.getMessage());
		}
	}
	
	//Editing the User fields
	public void editUser(User user) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(EDIT_USER);
			stmt.setString(1, user.getEmail());
			stmt.setString(2, user.getPassword());
			stmt.setString(3, user.getFirstName());
			stmt.setString(4, user.getLastName());
			stmt.setDate(5, (Date) user.getDateOfBirth());
			stmt.setString(6, user.getImageURL());
			stmt.setString(7, user.getSummary());
			stmt.setString(8, user.getCity());
			stmt.setString(9, user.getTel());
			stmt.setLong(10, user.getTitle());
			stmt.setDouble(11, user.getRating());
			stmt.setLong(12, user.getPopularity());
			stmt.setInt(13, user.isAvailable()?1:0);
			stmt.setInt(14, user.isReal()?1:0);
			stmt.setInt(15, user.isFemale()?1:0);
			stmt.setLong(16, user.getUid());
			
			stmt.execute();
			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with editing user information! "	+ e.getMessage());
		}
	}
	
	//Selection of the top Users 
	public LinkedList<User> selectTopUsers( int n ) throws DatabaseException
	{
		LinkedList<User> topUsers = new LinkedList<User>();
		conn = JDBCUtil.getConnection();
		
		User user = null;
		try {
			stmt = conn.prepareStatement(TOP_USER);
			stmt.setInt(1, n);
			
			rs = stmt.executeQuery();
			while (rs.next()) {
				long uid = rs.getLong("uid");
				String email = rs.getString("email");
				String password = rs.getString("password");
				String firstname = rs.getString("firstName");
				String lastname = rs.getString("lastName");
				String imageURL = rs.getString("imageURL");
				String summary = rs.getString("summary");
				String city = rs.getString("city");
				String tel = rs.getString("tel");
				Date dateOfBirth = rs.getDate("dateOfBirth");
				int available=rs.getInt("isAvailable");
				int isReal = rs.getInt("isReal");
				int isFemale = rs.getInt("isFemale");
				int title = rs.getInt("userTitle");
				int popularity = rs.getInt("popularity");
				double rating = rs.getInt("rating");
				
				Actor actor=actorService.getActorByUid(uid);
				
				user=new User(uid,email,password, firstname,lastname,dateOfBirth,imageURL, summary,city,tel,title,rating,popularity,actor,available==1,isReal==1,isFemale==1);
				topUsers.add(user);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving top users! "	+ e.getMessage());
		}
		return topUsers;
	}
	
}
